﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// <summary>
/// Summary description for DBHelper
/// </summary>
public class DBHelp
{
    private SqlConnection conn;
    private SqlCommand cmd;
    SqlDataAdapter adapter;
    public DBHelp()
    {
        if (conn == null || conn.State == ConnectionState.Closed)
            InitConnection();
    }
    private void InitConnection()
    {
        conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ruouconnection"].ConnectionString);
        conn.Open();

    }
    public void OpenConnection()
    {
        conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ruouconnection"].ConnectionString);
        conn.Open();
    }
    public void CloseConnection()
    {
        if (conn.State == ConnectionState.Open)
            conn.Close();
    }
    private void PrepareCommand(SqlCommand cmd, String SPName, List<SqlParameter> paramList)
    {
        if (conn == null || conn.State == ConnectionState.Closed)
            InitConnection();
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = SPName;
        cmd.Parameters.Clear();
        if (paramList != null)
        {
            foreach (SqlParameter param in paramList)
            {
                cmd.Parameters.Add(param);
            }
        }
    }
    public int ExecuteNonQuerry(String SPName, List<SqlParameter> paramList)
    {
        cmd = new SqlCommand();
        PrepareCommand(cmd, SPName, paramList);
        int i = cmd.ExecuteNonQuery();
        CloseConnection();
        return i;
    }

    public SqlDataAdapter ExecuteQuery_GetAdapter(String SPName, List<SqlParameter> paramList)
    {
        cmd = new SqlCommand();
        PrepareCommand(cmd, SPName, paramList);
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        CloseConnection();
        return da;
    }

    public SqlDataReader ExcuteQuery_GetDataReader(String SPName, List<SqlParameter> ListPara)
    {
        this.cmd = new SqlCommand();
        this.PrepareCommand(this.cmd, SPName, ListPara);
        return this.cmd.ExecuteReader();
    }

    public object ExcuterScalar(string SPName, List<SqlParameter> listparam)
    {
        this.cmd = new SqlCommand();
        PrepareCommand(this.cmd, SPName, listparam);
        object a = this.cmd.ExecuteScalar();
        CloseConnection();
        return a;
    }
    public DataSet GetAll(string SPName, List<SqlParameter> paramList)
    {
        cmd = new SqlCommand();
        PrepareCommand(cmd, SPName, paramList);
        adapter = new SqlDataAdapter();
        DataSet dtSet = new DataSet();
        PrepareCommand(cmd, SPName, paramList);
        adapter.SelectCommand = cmd;
        adapter.Fill(dtSet);
        CloseConnection();
        return dtSet;
    }
}